<?php

namespace fastphp\facade;

use fastphp\util\Log;
use PDO;
use PDOException;

final class Db
{
    private string $dsn = DB_TYPE . ':host=' . DB_HOST . ';dbname=' . DB_NAME . ';port=' . DB_PORT;
    private $link;
    private string $tableName = "";
    private string $sql;
    private string $fields = "";
    private string $wheres = "";
    private int $whereType = 0;//默认：0代表用AND连接，1代表用OR连接
    private string $aliasName = "";
    private string $joins = "";
    private int $joinType = 0;//默认：0代表内连接，1代表左连接，2代表有右连接
    private string $joinTabName = "";
    private string $joinAsName = "";
    private string $orders = "";
    private $offset = null;
    private $perPage;

    /**
     * 初始化
     */
    public function __construct()
    {
        $this->sql_connect();
    }

    /**
     * 创建类实例
     * @return Db 返回一个Db类实例用于链式调用
     */
    private static function create(): Db
    {
        return new self();
    }

    /**
     * 指定数据表
     * @param string $name 数据表的名称
     * @return Db 返回Db实例
     */
    public static function table(string $name): Db
    {
        $instance = self::create();
        if (is_string($name)) {
            $instance->tableName = DB_TABLE_PREFIX . $name;
        }
        return $instance;
    }

    /**
     * 创建数据库连接
     */
    private function sql_connect()
    {
        try {
            $this->link = new PDO($this->dsn, DB_USER, DB_PASSWORD);
            $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            die("数据库连接失败：" . $e->getMessage());
        }
    }

    /**
     * 执行Sql增删改语句
     * @param string $sql 执行的sql语句
     * @return int 返回受影响的行数
     */
    public function sql_exce(string $sql): int
    {
        try {
            $stmt = $this->link->prepare($sql);
            // 记录开始时间
            $startTime = microtime(true);
            $stmt->execute();
            // 记录结束时间
            $endTime = microtime(true);
            // 计算执行时间
            $executionTime = $endTime - $startTime;
            Log::dbLogWrite($sql, $executionTime);
            return $stmt->rowCount();
        } catch (PDOException $e) {
            Log::dbErrLogWrite($sql . '     ' . $e->getMessage());
            die('SQL Error: ' . $e->getMessage());
        }
    }

    /**
     * 执行Sql查询语句
     * @param string $sql 执行的sql语句
     * @param bool $fetchAll 是否获取所有结果集
     * @return array|mixed 返回结果集数组数据
     */
    public function sql_query(string $sql, bool $fetchAll = true)
    {
        try {
            $stmt = $this->link->prepare($sql);
            // 记录开始时间
            $startTime = microtime(true);
            $stmt->execute();
            // 记录结束时间
            $endTime = microtime(true);
            // 计算执行时间
            $executionTime = $endTime - $startTime;
            Log::dbLogWrite($sql, $executionTime);
            if ($fetchAll) {
                return $stmt->fetchAll(PDO::FETCH_ASSOC);
            } else {
                return $stmt->fetch(PDO::FETCH_ASSOC);
            }
        } catch (PDOException $e) {
            Log::dbErrLogWrite($sql . '     ' . $e->getMessage());
            die('SQL Error: ' . $e->getMessage());
        }
    }

    /**
     * 查看生成的Sql语句
     * @return string 请放在链式调用的最后
     */
    public function getSql()
    {
        $this->spliceSql();
        return $this->sql;
    }

    /**
     * 查询多个数据
     * @return array 返回查询的数据
     */
    public function select()
    {
        $this->spliceSql();
        return $this->sql_query($this->sql);
    }

    /**
     * 拼接sql语句
     */
    private function spliceSql()
    {
        if (empty($this->sql)) {
            $this->sql = "SELECT ";
        }
        if (!empty($this->fields)) {
            $this->sql .= $this->fields . " FROM ";
        } else {
            $this->sql .= "* FROM ";
        }

        if (!empty($this->tableName)) {
            $this->sql .= "`$this->tableName`";
        }

        if (!empty($this->aliasName)) {
            $this->sql .= " AS " . $this->aliasName;
        }

        if (!empty($this->joinTabName)) {
            if ($this->joinType == 0) {
                $this->sql .= " INNER JOIN `$this->joinTabName`";
            } elseif ($this->joinType == 1) {
                $this->sql .= " LEFT JOIN `$this->joinTabName`";
            } elseif ($this->joinType == 2) {
                $this->sql .= " RIGHT JOIN `$this->joinTabName`";
            }
        }

        if (!empty($this->joinAsName)) {
            $this->sql .= " AS " . $this->joinAsName;
        }

        if (!empty($this->joins)) {
            $this->sql .= " ON " . $this->joins;
        }

        if (!empty($this->wheres)) {
            $this->sql .= " WHERE " . $this->wheres;
        }

        if (!empty($this->orders)) {
            $this->sql .= " ORDER BY " . $this->orders;
        }
        if ($this->offset !== null) {
            $this->sql .= " LIMIT $this->offset, $this->perPage";
        }
    }

    /**
     * 查询单个数据
     * @return array 返回查询的数据
     */
    public function find()
    {
        $this->spliceSql();
        return $this->sql_query($this->sql, false);
    }

    /**
     * 修改方法
     * @param array $data 需要的修改参数
     * @return int   返回0或1：0代表失败，1代表成功
     */
    public function update($data)
    {
        if (is_array($data)) {
            $len = count($data);
            $index = 0;
            $this->sql = "UPDATE $this->tableName SET ";
            foreach ($data as $key => $value) {
                if ($index != $len - 1) {
                    if (is_string($value)) {
                        $this->sql .= "$key = '$value', ";
                    } else {
                        $this->sql .= "$key = $value, ";
                    }
                } else {
                    if (is_string($value)) {
                        $this->sql .= "$key = '$value'";
                    } else {
                        $this->sql .= "$key = $value";
                    }

                }
                $index++;
            }
            $this->sql .= " WHERE " . $this->wheres;
        }
        return $this->sql_exce($this->sql);
    }

    /**
     * 删除方法
     * @return int  返回0或1：0代表失败，1代表成功
     */
    public function delete()
    {
        if (!empty($this->wheres)) {
            $this->sql = "DELETE FROM " . $this->tableName;
            $this->sql .= " WHERE " . $this->wheres;
        } else {
            $this->sql = "DELETE FROM " . $this->tableName;
        }
        return $this->sql_exce($this->sql);
    }

    /**
     * 插入方法
     * @param array $data 需要的插入参数
     * @return int  返回0或1：0代表失败，1代表成功
     */
    public function insert($data)
    {
        if (is_array($data)) {
            $this->sql = "INSERT INTO " . $this->tableName;
            $keys = "";
            $values = "";
            $len = count($data);

            if (isset($data[0]) && is_array($data[0])) {
                $lineLen = count($data[0]);
                $index = 0;
                $keys .= "(";
                foreach ($data[0] as $key => $v) {
                    if ($index != $lineLen - 1) {
                        $keys .= $key . ',';
                    } else {
                        $keys .= $key;
                    }
                    $index++;
                }
                $keys .= ")";

                $index = 0;
                foreach ($data as $i => $value) {
                    $values .= "(";
                    foreach ($value as $key => $value) {
                        if ($index != $lineLen - 1) {
                            if (is_string($value)) {
                                $values .= "'$value',";
                            } else {
                                $values .= $value . ',';
                            }

                        } else {
                            if (is_string($value)) {
                                $values .= "'$value'";
                            } else {
                                $values .= $value;
                            }
                        }
                        $index++;
                    }
                    if ($i != $len - 1) {
                        $values .= '),';
                    } else {
                        $values .= ')';
                    }
                    $index = 0;
                }
            } else {
                $keys .= "(";
                $values .= "(";
                $index = 0;

                foreach ($data as $key => $value) {
                    if ($index != $len - 1) {
                        $keys .= $key . ',';
                        if (is_string($value)) {
                            $values .= "'$value',";
                        } else {
                            $values .= $value . ',';
                        }

                    } else {
                        $keys .= $key;
                        if (is_string($value)) {
                            $values .= "'$value'";
                        } else {
                            $values .= $value;
                        }
                    }
                    $index++;
                }
                $keys .= ")";
                $values .= ")";
            }
            $this->sql .= $keys . ' VALUES' . $values;
        }
        return $this->sql_exce($this->sql);
    }

    /**
     * 条件方法
     * @param array|string $firstExp 字符串或数组表达式
     * @param null|string $symbol 符号表达式
     * @param null|string|array $lastExp 字符串或数组表达式
     * @return $this    返回当前对象
     */
    public function where($firstExp, $symbol = null, $lastExp = null)
    {
        $exp = $this->whereType ? ' OR ' : ' AND ';
        if (is_array($firstExp)) {
            $len = count($firstExp);
            for ($i = 0; $i < $len; $i++) {
                if (!empty($this->wheres)) {
                    if (is_string($firstExp[$i][2])) {
                        $this->wheres .= $exp . $firstExp[$i][0] . $firstExp[$i][1] . "'" . $firstExp[$i][2] . "'";
                    } else {
                        $this->wheres .= $exp . $firstExp[$i][0] . $firstExp[$i][1] . $firstExp[$i][2];
                    }
                } else {
                    if (is_string($firstExp[$i][2])) {
                        $this->wheres .= $firstExp[$i][0] . $firstExp[$i][1] . "'" . $firstExp[$i][2] . "'";
                    } else {
                        $this->wheres .= $firstExp[$i][0] . $firstExp[$i][1] . $firstExp[$i][2];
                    }
                }
                /*if ($i!=$len-1) {
                    // $this->wheres.=$exp;
                }*/
            }
        } else {
            if (empty($lastExp)) {
                if (!empty($this->wheres)) {
                    if (is_string($symbol)) {
                        $this->wheres .= $exp . $firstExp . ' = ' . "'" . $symbol . "'";
                    } else {
                        $this->wheres .= $exp . $firstExp . ' = ' . $symbol;
                    }

                } else {
                    if (is_string($symbol)) {
                        $this->wheres .= $firstExp . ' = ' . "'" . $symbol . "'";
                    } else {
                        $this->wheres .= $firstExp . ' = ' . $symbol;
                    }
                }
            } else {
                if (!empty($this->wheres)) {
                    if (is_string($lastExp)) {
                        $this->wheres .= " $exp $firstExp $symbol '$lastExp'";
                    } else {
                        $this->wheres .= " $exp $firstExp $symbol $lastExp";
                    }
                } else {
                    if (is_string($lastExp)) {
                        $this->wheres .= "$firstExp $symbol '$lastExp'";
                    } elseif (is_array($lastExp)) {
                        if ($symbol == 'in') {
                            $tmp = "(";
                            $len = count($lastExp);
                            for ($j = 0; $j < $len; $j++) {
                                if ($j == ($len - 1)) {
                                    if (is_string($lastExp[$j])) {
                                        $tmp .= "'" . $lastExp[$j] . "'";
                                    } else {
                                        $tmp .= $lastExp[$j];
                                    }
                                } else {
                                    if (is_string($lastExp[$j])) {
                                        $tmp .= "'" . $lastExp[$j] . "',";
                                    } else {
                                        $tmp .= $lastExp[$j] . ',';
                                    }
                                }
                            }
                            $tmp .= ")";
                            $upperSymbol = strtoupper($symbol);
                            $this->wheres .= "$firstExp $upperSymbol $tmp";
                        }
                    } else {
                        $this->wheres .= "$firstExp $symbol $lastExp";
                    }

                }
            }

        }

        return $this;
    }

    /**
     * 条件方法
     * @param array|string $firstExp 字符串或数组表达式
     * @param null|string $symbol 符号表达式
     * @param null|string $lastExp 字符串或数组表达式
     * @return $this   返回当前对象
     */
    public function whereOr($firstExp, $symbol, $lastExp)
    {
        return $this->where($firstExp, $symbol, $lastExp);
    }

    /**
     * 查询指定字段
     * @param string $data 需要查询的字段
     * @return $this   返回当前对象
     */
    public function field($data)
    {
        if (is_string($data)) {
            $this->fields = $data;
        }
        return $this;
    }

    /**
     * 设置表别名
     * @param string $name 表别名
     * @return $this   返回当前对象
     */
    public function alias($name)
    {
        if (is_string($name)) {
            $this->aliasName = $name;
        }
        return $this;
    }

    /**
     * 表连接
     * @param string $name 需要连接的表名
     * @param string $exp 连接条件
     * @return $this   返回当前对象
     */
    public function join($name, $exp)
    {
        $n = explode(" ", DB_TABLE_PREFIX . $name);
        $str = "";
        if (is_array($exp)) {
            $len = count($exp);
            foreach ($exp as $i => $value) {
                $str .= "$value[0] $value[1] $value[2]";
                if ($i != $len - 1) {
                    $str .= " AND ";
                }
            }
        } else {
            $str .= $exp;
        }
        $this->joins = $str;
        $this->joinTabName = $n[0];
        $this->joinAsName = $n[1];
        return $this;
    }

    /**
     * 左连接
     * @param string $name 需要连接的表名
     * @param string $exp 连接条件
     * @return $this   返回当前对象
     */
    public function leftJoin($name, $exp)
    {
        $this->joinType = 1;
        return $this->join($name, $exp);
    }

    /**
     * 右连接
     * @param string $name 需要连接的表名
     * @param string $exp 连接条件
     * @return $this   返回当前对象
     */
    public function rightJoin($name, $exp)
    {
        $this->joinType = 2;
        return $this->join($name, $exp);
    }

    /**
     * 排序
     * @param string $data 需要排序的字段
     * @return $this 返回当前实例对象
     */
    public function order($data)
    {
        if (is_string($data)) {
            $this->orders = $data;
        }
        return $this;
    }

    /**
     * 分页
     * @param integer $start 起始索引
     * @param integer $end 结束索引
     * @return $this   返回对象本身
     */
    public function limit(int $start, int $end): Db
    {
        $this->offset = $start;
        $this->perPage = $end;
        return $this;
    }

    /**
     * 自动计算分页
     * @param integer $page 页码
     * @param integer $perPage 每页的个数
     * @return $this   返回对象本身
     */
    public function page($page, $perPage)
    {
        // 计算偏移量
        $offset = ($page - 1) * $perPage;
        $this->offset = $offset;
        $this->perPage = $perPage;
        return $this;
    }

    /**
     * 获取总记录数（用于计算总页数）
     * @return integer 数据表总记录数
     */
    public function getTotalRecords()
    {
        $sql = "SELECT COUNT(*) as total FROM $this->tableName";
        return $this->sql_query($sql, false)['total'];
    }
}
